﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Wicresoft.UnifyShow.Business;
using Oracle.DataAccess.Client;
using System.Data;

namespace Wicresoft.UnifyShow.BusinessImpl
{
    public class Incident : Basic, IIncident
    {
        private const string sqlGetIncidentBySiteIDBSIDAndDeviceID = "select i.*  from incident i inner join alert a on i.Request_ID=a.ticketid" +
      " inner join device d on a.deviceid=d.id where d.siteid=:siteID and d.businesssystemid=:businessSystemID and d.id=:deviceID ";

        //地市总事件数
        public Decimal GetCountIncident(int? site, int? bnid, DateTime? sTime, DateTime? eTime)
        {
            List<OracleParameter> cmdParms = new List<OracleParameter>();
            string sqlStr = "select Count(*) from Incident where status >=0 and status <=6";
            if (site != null)
            {
                sqlStr += " and siteid=:siteid";
                cmdParms.Add(new OracleParameter("siteid", OracleDbType.Int32, site, ParameterDirection.Input));
            }
            if (bnid != null)
            {
                sqlStr += " and BusinessSystemId = :bnid";
                cmdParms.Add(new OracleParameter("bnid", OracleDbType.Int32, bnid, ParameterDirection.Input));
            }
            if (sTime != null)
            {
                sqlStr += " and TicketCreateTime >= :sTime";
                cmdParms.Add(new OracleParameter("sTime", OracleDbType.Date, sTime, ParameterDirection.Input));
            }
            if (eTime != null)
            {
                sqlStr += " and TicketCreateTime <= :eTime";
                cmdParms.Add(new OracleParameter("eTime", OracleDbType.Date, eTime, ParameterDirection.Input));
            }
            return ModelHelper<IncidentModel>.GetModelCount_By_DirectSql(sqlStr, cmdParms, oda);
        }

        //总事件List
        public List<IncidentAndChangeModel> GetIncidentList(int? site, int? bnid, DateTime? sTime, DateTime? eTime)
        {
            List<OracleParameter> cmdParms = new List<OracleParameter>();
            string sqlStr = "select Count(*) alls,to_char(TicketCreateTime, 'yyyyMM') sign from Incident where status >=0 and status <=6";
            if (site != null)
            {
                sqlStr += " and siteid=:siteid";
                cmdParms.Add(new OracleParameter("siteid", OracleDbType.Int32, site, ParameterDirection.Input));
            }
            if (bnid != null)
            {
                sqlStr += " and BusinessSystemId = :bnid";
                cmdParms.Add(new OracleParameter("bnid", OracleDbType.Int32, bnid, ParameterDirection.Input));
            }
            if (sTime != null)
            {
                sqlStr += " and TicketCreateTime >= :sTime";
                cmdParms.Add(new OracleParameter("sTime", OracleDbType.Date, sTime, ParameterDirection.Input));
            }
            if (eTime != null)
            {
                sqlStr += " and TicketCreateTime <= :eTime";
                cmdParms.Add(new OracleParameter("eTime", OracleDbType.Date, eTime, ParameterDirection.Input));
            }
            sqlStr += " group by to_char(TicketCreateTime, 'yyyyMM')";
            OracleDataReader dr = oda.ExecuteReader(sqlStr, cmdParms);
            return ModelHelper<IncidentAndChangeModel>.GetModel_By_OracleDataReader(dr);
        }

        //查询地市未解决事件(status <  4)
        public List<IncidentModel> GetUnResolvedIncidentList(int? site, DateTime? sTime, DateTime? eTime, int? ps, int? pi)
        {
            List<OracleParameter> cmdParms = new List<OracleParameter>();
            string sqlStr = string.Empty;
            string whereStr = string.Empty;

            if (site != null)
            {
                whereStr += " and siteid=:siteid";
                cmdParms.Add(new OracleParameter("siteid", OracleDbType.Int32, site, ParameterDirection.Input));
            }

            if (sTime != null)
            {
                whereStr += " and TicketCreateTime >= :sTime";
                cmdParms.Add(new OracleParameter("sTime", OracleDbType.Date, sTime, ParameterDirection.Input));
            }
            if (eTime != null)
            {
                whereStr += " and TicketCreateTime <= :eTime";
                cmdParms.Add(new OracleParameter("eTime", OracleDbType.Date, eTime, ParameterDirection.Input));
            }

            if (ps.HasValue && pi.HasValue)
            {
                int beginIndex = pi.Value * ps.Value;
                int endIndex = (pi.Value + 1) * ps.Value;
                sqlStr = "select * from ( select incident.*,row_number() over ( order by TicketCreateTime desc) rn from incident where status < 4 " + whereStr + " ) where rn between " + beginIndex + " and " + endIndex;
            }
            else
            {
                sqlStr = "select * from Incident where status < 4 " + whereStr;
            }
            OracleDataReader dr = oda.ExecuteReader(sqlStr, cmdParms);
            return ModelHelper<IncidentModel>.GetModel_By_OracleDataReader(dr);
        }

        //地市已解决事件数(status >= 4)
        public Decimal GetApproveCountIncident(int? site, int? bnid, DateTime? sTime, DateTime? eTime)
        {
            List<OracleParameter> cmdParms = new List<OracleParameter>();
            string sqlStr = "select Count(*) from Incident where status >= 4 and status <=6";
            if (site != null)
            {
                sqlStr += " and siteid=:siteid";
                cmdParms.Add(new OracleParameter("siteid", OracleDbType.Int32, site, ParameterDirection.Input));
            }
            if (bnid != null)
            {
                sqlStr += " and BusinessSystemId = :bnid";
                cmdParms.Add(new OracleParameter("bnid", OracleDbType.Int32, bnid, ParameterDirection.Input));
            }
            if (sTime != null)
            {
                sqlStr += " and TicketCreateTime >= :sTime";
                cmdParms.Add(new OracleParameter("sTime", OracleDbType.Date, sTime, ParameterDirection.Input));
            }
            if (eTime != null)
            {
                sqlStr += " and TicketCreateTime <= :eTime";
                cmdParms.Add(new OracleParameter("eTime", OracleDbType.Date, eTime, ParameterDirection.Input));
            }
            return ModelHelper<IncidentModel>.GetModelCount_By_DirectSql(sqlStr, cmdParms, oda);
        }

        //规定时间内解决的事件数量(ResolvedTimeOut < 0) (未超时)
        public Decimal GetCountIncidentByResolvedTimeOut(int? site, int? bnid, DateTime? sTime, DateTime? eTime)
        {
            List<OracleParameter> cmdParms = new List<OracleParameter>();
            string sqlStr = "select Count(*) from Incident where (ResolvedTimeOut < 0 or ResolvedTimeOut is null) and status >= 4 and status <=6";
            if (site != null)
            {
                sqlStr += " and siteid=:siteid";
                cmdParms.Add(new OracleParameter("siteid", OracleDbType.Int32, site, ParameterDirection.Input));
            }
            if (bnid != null)
            {
                sqlStr += " and BusinessSystemId = :bnid";
                cmdParms.Add(new OracleParameter("bnid", OracleDbType.Int32, bnid, ParameterDirection.Input));
            }
            if (sTime != null)
            {
                sqlStr += " and TicketCreateTime >= :sTime";
                cmdParms.Add(new OracleParameter("sTime", OracleDbType.Date, sTime, ParameterDirection.Input));
            }
            if (eTime != null)
            {
                sqlStr += " and TicketCreateTime <= :eTime";
                cmdParms.Add(new OracleParameter("eTime", OracleDbType.Date, eTime, ParameterDirection.Input));
            }
            return ModelHelper<IncidentModel>.GetModelCount_By_DirectSql(sqlStr, cmdParms, oda);
        }

        //平均解决时间 (avg(TicketResolvedTime-TicketCreateTime))
        public List<IncidentAndChangeModel> GetCountIncidentAvgtmeBySite(int? site, int? bnid, DateTime? sTime, DateTime? eTime)
        {
            List<OracleParameter> cmdParms = new List<OracleParameter>();
            string sqlStr = "select round(avg(TicketResolvedTime-TicketCreateTime),4)*24 alls,to_char(TicketCreateTime, 'yyyyMM') sign from incident where status >=4 and status <=6";
            if (site != null)
            {
                sqlStr += " and siteid=:siteid";
                cmdParms.Add(new OracleParameter("siteid", OracleDbType.Int32, site, ParameterDirection.Input));
            }
            if (bnid != null)
            {
                sqlStr += " and BusinessSystemId = :bnid";
                cmdParms.Add(new OracleParameter("bnid", OracleDbType.Int32, bnid, ParameterDirection.Input));
            }
            if (sTime != null)
            {
                sqlStr += " and TicketCreateTime >= :sTime ";
                cmdParms.Add(new OracleParameter("sTime", OracleDbType.Date, sTime, ParameterDirection.Input));
            }
            if (eTime != null)
            {
                sqlStr += " and TicketCreateTime <= :eTime ";
                cmdParms.Add(new OracleParameter("eTime", OracleDbType.Date, eTime, ParameterDirection.Input));
            }
            sqlStr += " group by to_char(TicketCreateTime, 'yyyyMM')";
            OracleDataReader dr = oda.ExecuteReader(sqlStr, cmdParms);
            return ModelHelper<IncidentAndChangeModel>.GetModel_By_OracleDataReader(dr);
        }

        //一线解决率（服务台+现场工程师ResolvedGroup < 2）
        public List<IncidentAndChangeModel> GetCountIncidentByResolvedGroup1(int? site, int? bnid, DateTime? sTime, DateTime? eTime)
        {
            List<OracleParameter> cmdParms = new List<OracleParameter>();
            string sqlStr = "select Count(*) alls,to_char(TicketCreateTime, 'yyyyMM') sign from Incident where ResolvedGroup < 2 and ResolvedGroup >=0 and status >= 4 and status <=6";
            if (site != null)
            {
                sqlStr += " and siteid=:siteid";
                cmdParms.Add(new OracleParameter("siteid", OracleDbType.Int32, site, ParameterDirection.Input));
            }
            if (bnid != null)
            {
                sqlStr += " and BusinessSystemId = :bnid";
                cmdParms.Add(new OracleParameter("bnid", OracleDbType.Int32, bnid, ParameterDirection.Input));
            }
            if (sTime != null)
            {
                sqlStr += " and TicketCreateTime >= :sTime";
                cmdParms.Add(new OracleParameter("sTime", OracleDbType.Date, sTime, ParameterDirection.Input));
            }
            if (eTime != null)
            {
                sqlStr += " and TicketCreateTime <= :eTime";
                cmdParms.Add(new OracleParameter("eTime", OracleDbType.Date, eTime, ParameterDirection.Input));
            }
            sqlStr += " group by  to_char(TicketCreateTime, 'yyyyMM')";
            OracleDataReader dr = oda.ExecuteReader(sqlStr, cmdParms);
            return ModelHelper<IncidentAndChangeModel>.GetModel_By_OracleDataReader(dr);
        }

        //二线解决率（ResolvedGroup >= 2）
        public List<IncidentAndChangeModel> GetCountIncidentByResolvedGroup2(int? site, int? bnid, DateTime? sTime, DateTime? eTime)
        {
            List<OracleParameter> cmdParms = new List<OracleParameter>();
            string sqlStr = "select Count(*) alls,to_char(TicketCreateTime, 'yyyyMM') sign from Incident where ResolvedGroup >= 2 and ResolvedGroup <=5 and status >= 4 and status <=6";
            if (site != null)
            {
                sqlStr += " and siteid=:siteid";
                cmdParms.Add(new OracleParameter("siteid", OracleDbType.Int32, site, ParameterDirection.Input));
            }
            if (bnid != null)
            {
                sqlStr += " and BusinessSystemId = :bnid";
                cmdParms.Add(new OracleParameter("bnid", OracleDbType.Int32, bnid, ParameterDirection.Input));
            }
            if (sTime != null)
            {
                sqlStr += " and TicketCreateTime >= :sTime ";
                cmdParms.Add(new OracleParameter("sTime", OracleDbType.Date, sTime, ParameterDirection.Input));
            }
            if (eTime != null)
            {
                sqlStr += " and TicketCreateTime <= :eTime ";
                cmdParms.Add(new OracleParameter("eTime", OracleDbType.Date, eTime, ParameterDirection.Input));
            }
            sqlStr += " group by to_char(TicketCreateTime, 'yyyyMM')";
            OracleDataReader dr = oda.ExecuteReader(sqlStr, cmdParms);
            return ModelHelper<IncidentAndChangeModel>.GetModel_By_OracleDataReader(dr);
        }

        //重大事件数量/百分比（Impact=1000 '重大'）
        public List<IncidentAndChangeModel> GetSeriousCountIncidentByImpact(int? site, int? bnid, DateTime? sTime, DateTime? eTime)
        {
            List<OracleParameter> cmdParms = new List<OracleParameter>();
            string sqlStr = "select Count(*) alls,to_char(TicketCreateTime, 'yyyyMM') sign from Incident where Impact = 1000 and status >= 4 and status <=6";
            if (site != null)
            {
                sqlStr += " and siteid=:siteid";
                cmdParms.Add(new OracleParameter("siteid", OracleDbType.Int32, site, ParameterDirection.Input));
            }
            if (bnid != null)
            {
                sqlStr += " and BusinessSystemId = :bnid";
                cmdParms.Add(new OracleParameter("bnid", OracleDbType.Int32, bnid, ParameterDirection.Input));
            }
            if (sTime != null)
            {
                sqlStr += " and TicketCreateTime >= :sTime";
                cmdParms.Add(new OracleParameter("sTime", OracleDbType.Date, sTime, ParameterDirection.Input));
            }
            if (eTime != null)
            {
                sqlStr += " and TicketCreateTime <= :eTime";
                cmdParms.Add(new OracleParameter("eTime", OracleDbType.Date, eTime, ParameterDirection.Input));
            }
            sqlStr += " group by  to_char(TicketCreateTime, 'yyyyMM')";
            OracleDataReader dr = oda.ExecuteReader(sqlStr, cmdParms);
            return ModelHelper<IncidentAndChangeModel>.GetModel_By_OracleDataReader(dr);
        }

        //一般事件数量 
        public List<IncidentAndChangeModel> GetGeneralCountIncidentByImpact(int? site, int? bnid, DateTime? sTime, DateTime? eTime)
        {
            List<OracleParameter> cmdParms = new List<OracleParameter>();
            string sqlStr = "select Count(*) alls,to_char(TicketCreateTime, 'yyyyMM') sign from Incident where Impact != 1000 and status >= 4 and status <=6";
            if (site != null)
            {
                sqlStr += " and siteid=:siteid";
                cmdParms.Add(new OracleParameter("siteid", OracleDbType.Int32, site, ParameterDirection.Input));
            }
            if (bnid != null)
            {
                sqlStr += " and BusinessSystemId = :bnid";
                cmdParms.Add(new OracleParameter("bnid", OracleDbType.Int32, bnid, ParameterDirection.Input));
            }
            if (sTime != null)
            {
                sqlStr += " and TicketCreateTime >= :sTime";
                cmdParms.Add(new OracleParameter("sTime", OracleDbType.Date, sTime, ParameterDirection.Input));
            }
            if (eTime != null)
            {
                sqlStr += " and TicketCreateTime <= :eTime";
                cmdParms.Add(new OracleParameter("eTime", OracleDbType.Date, eTime, ParameterDirection.Input));
            }
            sqlStr += " group by  to_char(TicketCreateTime, 'yyyyMM')";
            OracleDataReader dr = oda.ExecuteReader(sqlStr, cmdParms);
            return ModelHelper<IncidentAndChangeModel>.GetModel_By_OracleDataReader(dr);
        }

        //事件升级率
        public Decimal GetIncidentEscalationBySite(int? site, int? bnid, DateTime? sTime, DateTime? eTime)
        {
            List<OracleParameter> cmdParms = new List<OracleParameter>();
            string sqlStr = "select Count(distinct e.sourceid) from escalation e inner join Incident i on e.sourceid = i.request_id inner join Incident i1 on e.DESTINATIONID = i1.request_id where i1.siteid = '101' and i.siteid != '101'and e.sourcetype =1";
            if (site != null)
            {
                sqlStr += " and i.siteid=:siteid";
                cmdParms.Add(new OracleParameter("siteid", OracleDbType.Int32, site, ParameterDirection.Input));
            }
            if (bnid != null)
            {
                sqlStr += " and i.BusinessSystemId = :bnid";
                cmdParms.Add(new OracleParameter("bnid", OracleDbType.Int32, bnid, ParameterDirection.Input));
            }
            if (sTime != null)
            {
                sqlStr += " and i.TicketCreateTime >= :sftime";
                cmdParms.Add(new OracleParameter("sftime", OracleDbType.Date, sTime, ParameterDirection.Input));
            }
            if (eTime != null)
            {
                sqlStr += " and i.TicketCreateTime <= :eTime";
                cmdParms.Add(new OracleParameter("eTime", OracleDbType.Date, eTime, ParameterDirection.Input));
            }
            return ModelHelper<ChangeModel>.GetModelCount_By_DirectSql(sqlStr, cmdParms, oda);
        }

        //按照事件结束代码分类(根本解决，无法解决)
        public List<IncidentAndChangeModel> GetIncidentCloseCodeBysite(int? site, int? bnid, DateTime? sTime, DateTime? eTime)
        {
            List<OracleParameter> cmdParms = new List<OracleParameter>();
            string sqlStr = "select Count(*) alls,CloseCode Id from Incident where status >=4 and status <=6 and CloseCode <> '0'";
            if (site != null)
            {
                sqlStr += " and siteid=:site";
                cmdParms.Add(new OracleParameter("site", OracleDbType.Int32, site, ParameterDirection.Input));
            }
            if (bnid != null)
            {
                sqlStr += " and BusinessSystemId = :bnid";
                cmdParms.Add(new OracleParameter("bnid", OracleDbType.Int32, bnid, ParameterDirection.Input));
            }
            if (sTime != null)
            {
                sqlStr += " and TicketCreateTime >= :sTime";
                cmdParms.Add(new OracleParameter("sTime", OracleDbType.Date, sTime, ParameterDirection.Input));
            }
            if (eTime != null)
            {
                sqlStr += " and TicketCreateTime <= :eTime";
                cmdParms.Add(new OracleParameter("eTime", OracleDbType.Date, eTime, ParameterDirection.Input));
            }
            sqlStr += " group by CloseCode";
            OracleDataReader dr = oda.ExecuteReader(sqlStr, cmdParms);
            return ModelHelper<IncidentAndChangeModel>.GetModel_By_OracleDataReader(dr);
        }

        //服务统计 所有事件事件 未解决事件
        public List<SiteBranchAndWorkListModel> GetIncidentInfoList(int? site, int? bnid, DateTime? sTime, DateTime? eTime)
        {
            List<OracleParameter> cmdParms = new List<OracleParameter>();
            string sqlStr = "select t1.alias alls,t1.count1 sign,t2.count2 sign2 from (select Count(*) count1,s.alias from incident i inner join sitebranch s on i.siteid = s.id where status >=0 and status <=6";
            if (site != null)
            {
                sqlStr += " and i.SiteId = :siteid";
                cmdParms.Add(new OracleParameter("siteid", OracleDbType.Int32, site, ParameterDirection.Input));
            }
            if (bnid != null)
            {
                sqlStr += " and i.BusinessSystemId = :bnid";
                cmdParms.Add(new OracleParameter("bnid", OracleDbType.Int32, bnid, ParameterDirection.Input));
            }
            if (sTime != null)
            {
                sqlStr += " and i.TicketCreateTime >= :sTime";
                cmdParms.Add(new OracleParameter("sTime", OracleDbType.Date, sTime, ParameterDirection.Input));
            }
            if (eTime != null)
            {
                sqlStr += " and i.TicketCreateTime <= :eTime";
                cmdParms.Add(new OracleParameter("eTime", OracleDbType.Date, eTime, ParameterDirection.Input));
            }
            sqlStr += " group by s.alias ) t1 left join (select Count(*) count2,s2.alias from incident i2 inner join sitebranch s2 on i2.siteid = s2.id where i2.status < 4 and i2.status >=0";
            if (site != null)
            {
                sqlStr += " and i2.SiteId = :siteid2";
                cmdParms.Add(new OracleParameter("siteid2", OracleDbType.Int32, site, ParameterDirection.Input));
            }
            if (bnid != null)
            {
                sqlStr += " and i2.BusinessSystemId = :bnid2";
                cmdParms.Add(new OracleParameter("bnid2", OracleDbType.Int32, bnid, ParameterDirection.Input));
            }
            if (sTime != null)
            {
                sqlStr += " and i2.TicketCreateTime >= :sTime2";
                cmdParms.Add(new OracleParameter("sTime2", OracleDbType.Date, sTime, ParameterDirection.Input));
            }
            if (eTime != null)
            {
                sqlStr += " and i2.TicketCreateTime <= :eTime2";
                cmdParms.Add(new OracleParameter("eTime2", OracleDbType.Date, eTime, ParameterDirection.Input));
            }
            sqlStr += " group by s2.alias) t2 on t1.alias =t2.alias";
            OracleDataReader dr = oda.ExecuteReader(sqlStr, cmdParms);
            return ModelHelper<SiteBranchAndWorkListModel>.GetModel_By_OracleDataReader(dr);
        }

        public List<IncidentModel> GetPageAndSortIncidentBySiteIDAndBSID(int siteID, int bsID, out Decimal totalRecords, int pageSize = -1, int pageIndex = -1,
            string sortName = "TicketCreateTime", string sortDirection = "Descending"/*Ascending,Descending*/)
        {
            OrderbyParameter op = SQLHelper.GetOrderbyParameterByColumnNameAndSort(sortName, sortDirection);
            List<QueryParameter> attParam = new List<QueryParameter>()
            {
                        new QueryParameter(){ OracleDbColumnName="SiteID", OracleDbColumnType=OracleDbType.Decimal,
                        OracleDbColumnValue=siteID,QueryOperator=QueryOperatorType.equal,PredicateType=PredicateType.where},
                        new QueryParameter(){ OracleDbColumnName="BusinessSystemId", OracleDbColumnType=OracleDbType.Decimal,
                        OracleDbColumnValue=bsID,QueryOperator=QueryOperatorType.equal,PredicateType=PredicateType.where}
            };
            totalRecords = ModelHelper<IncidentModel>.GetModelCount_By_QueryParams(attParam, oda);
            return ModelHelper<IncidentModel>.GetModel_By_QueryParams(attParam, new List<OrderbyParameter>() { op }, pageSize, pageIndex, oda);
        }

        public List<IncidentModel> GetPageAndSortIncidentBySiteID(int siteID, out Decimal totalRecords, int pageSize = -1, int pageIndex = -1,
            string sortName = "TicketCreateTime", string sortDirection = "Descending"/*Ascending,Descending*/)
        {
            OrderbyParameter op = SQLHelper.GetOrderbyParameterByColumnNameAndSort(sortName, sortDirection);
            List<QueryParameter> attParam = new List<QueryParameter>() {
                    new QueryParameter(){ OracleDbColumnName="SiteID", OracleDbColumnType=OracleDbType.Decimal,
                        OracleDbColumnValue=siteID,QueryOperator=QueryOperatorType.equal,PredicateType=PredicateType.where}
                    };
            totalRecords = ModelHelper<IncidentModel>.GetModelCount_By_QueryParams(attParam, oda);
            return ModelHelper<IncidentModel>.GetModel_By_QueryParams(attParam, new List<OrderbyParameter>() { op }, pageSize, pageIndex, oda);
        }

        public List<IncidentModel> GetPageAndSortIncidentBySiteIDBSIDAndDeviceID(int siteID, int bsID, int deviceID, out Decimal totalRecords, int pageSize = -1, int pageIndex = -1,
            string sortName = "TicketCreateTime", string sortDirection = "Descending"/*Ascending,Descending*/)
        {
            List<OracleParameter> listPara = new List<OracleParameter>()
                 {
                     new OracleParameter(){ ParameterName="siteID",OracleDbType=OracleDbType.Decimal,Value=siteID},
                     new OracleParameter(){ ParameterName="businessSystemID",OracleDbType=OracleDbType.Decimal,Value=siteID},
                     new OracleParameter(){ ParameterName="deviceID",OracleDbType=OracleDbType.Decimal,Value=siteID}
                 };
            //need implement in the future by join form, possibility the incident need add a field 
            var copy = new List<OracleParameter>();
            listPara.ForEach(item => copy.Add((OracleParameter)(item.Clone())));
            totalRecords = ModelHelper<IncidentModel>.GetModelCount_By_DirectSql(SQLHelper.GetCountSQLFormDirectSQL(sqlGetIncidentBySiteIDBSIDAndDeviceID),
                listPara, oda);
            return ModelHelper<IncidentModel>.GetModel_By_DirectSql(SQLHelper.GetPageSQLFormDirectSQL(sqlGetIncidentBySiteIDBSIDAndDeviceID,
                sortName, sortDirection, pageSize, pageIndex), copy, oda);
        }

        //服务统计 所有事件事件 未解决事件
        public List<SiteBranchAndWorkListModel> GetIncidentInfoListBySystemName(int? site, string systemname, DateTime? sTime, DateTime? eTime)
        {
            List<OracleParameter> cmdParms = new List<OracleParameter>();
            string sqlStr = "select t1.alias alls,sb.id siteid, t1.count1 sign,t2.count2 sign2 from (select Count(*) count1,s.alias from incident i inner join sitebranch s on i.siteid = s.id where status >=0 and status <=6";
            if (site != null)
            {
                sqlStr += " and i.SiteId = :siteid";
                cmdParms.Add(new OracleParameter("siteid", OracleDbType.Int32, site, ParameterDirection.Input));
            }
            if (!string.IsNullOrEmpty(systemname))
            {
                sqlStr += " and i.BusinessSystemId in (select id from BusinessSystem  where name = :systemname)";
                cmdParms.Add(new OracleParameter("systemname", OracleDbType.NVarchar2, systemname, ParameterDirection.Input));
            }
            if (sTime != null)
            {
                sqlStr += " and i.TicketCreateTime >= :sTime";
                cmdParms.Add(new OracleParameter("sTime", OracleDbType.Date, sTime, ParameterDirection.Input));
            }
            if (eTime != null)
            {
                sqlStr += " and i.TicketCreateTime <= :eTime";
                cmdParms.Add(new OracleParameter("eTime", OracleDbType.Date, eTime, ParameterDirection.Input));
            }
            sqlStr += " group by s.alias ) t1 left join (select Count(*) count2,s2.alias from incident i2 inner join sitebranch s2 on i2.siteid = s2.id where i2.status < 4 and i2.status >=0";
            if (site != null)
            {
                sqlStr += " and i2.SiteId = :siteid2";
                cmdParms.Add(new OracleParameter("siteid2", OracleDbType.Int32, site, ParameterDirection.Input));
            }
            if (!string.IsNullOrEmpty(systemname))
            {
                sqlStr += " and i2.BusinessSystemId in (select id from BusinessSystem  where name = :systemname2)";
                cmdParms.Add(new OracleParameter("systemname2", OracleDbType.NVarchar2, systemname, ParameterDirection.Input));
            }
            if (sTime != null)
            {
                sqlStr += " and i2.TicketCreateTime >= :sTime2";
                cmdParms.Add(new OracleParameter("sTime2", OracleDbType.Date, sTime, ParameterDirection.Input));
            }
            if (eTime != null)
            {
                sqlStr += " and i2.TicketCreateTime <= :eTime2";
                cmdParms.Add(new OracleParameter("eTime2", OracleDbType.Date, eTime, ParameterDirection.Input));
            }
            sqlStr += " group by s2.alias) t2 on t1.alias =t2.alias left join sitebranch sb on sb.alias=t1.alias";
            OracleDataReader dr = oda.ExecuteReader(sqlStr, cmdParms);
            return ModelHelper<SiteBranchAndWorkListModel>.GetModel_By_OracleDataReader(dr);
        }
    }
}
